package com.company.corelib.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.company.corelib.model.*;
import com.company.corelib.util.Util;

public class CategoryDAO {
	private Connection connect = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet rs = null;

	public List<Category> getAllCategory() {
		List<Category> categories = new ArrayList<Category>();
		try {
			connect = Util.getConnection();
			statement = connect.createStatement();
			rs = statement
			          .executeQuery("select categoryid,code,name,description from category");
			 while (rs.next()) {
				 Category category = new Category();
				 category.setId(Integer.parseInt(rs.getString("categoryid")));
				 category.setCode(rs.getString("code"));
				 category.setName(rs.getString("name"));
				 category.setDescription(rs.getString("description"));
				 categories.add(category);
			 }
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Util.close(connect,preparedStatement,rs);
		}
		return categories;
	}
	public String create(String code, String name, String description) {
		String check = "true";
		try {
			connect = Util.getConnection();
			preparedStatement = connect
					.prepareStatement("insert into category(code,name,description) values(?,?,?)");
			preparedStatement.setString(1, code);
			preparedStatement.setString(2, name);
			preparedStatement.setString(3, description);
			preparedStatement.executeUpdate();
			check = "true";
		} catch (Exception e) {
			check = "false";
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Util.close(connect,preparedStatement,rs);
		}
		return check;
	}
	public String update(Category category) {
		String check = "true";
		try {
			connect = Util.getConnection();
			preparedStatement = connect
					.prepareStatement("update category set code = ?, name = ? , description = ? where categoryid=?");
			preparedStatement.setString(1, category.getCode());
			preparedStatement.setString(2, category.getName());
			preparedStatement.setString(3, category.getDescription());
			preparedStatement.setInt(4, category.getId());
			preparedStatement.executeUpdate();
			check = "true";
		} catch (Exception e) {
			check = "false";
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Util.close(connect,preparedStatement,rs);
		}
		System.err.println("Check = " + check);
		return check;
	}
	
	public Category getCategoryById(String id){
		 Category category = new Category();
		try {
			connect = Util.getConnection();
			statement = connect.createStatement();
			rs = statement
			          .executeQuery("select categoryid,code,name,description from category where categoryid='"+id + "'");
			 while (rs.next()) {
				 category.setId(Integer.parseInt(rs.getString("categoryid")));
				 category.setCode(rs.getString("code"));
				 category.setName(rs.getString("name"));
				 category.setDescription(rs.getString("description"));
			 }
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Util.close(connect,preparedStatement,rs);
		}
		return category;		
	}
	
	public String delete(String id) {
		String check = "true";
		try {
			connect = Util.getConnection();
			preparedStatement = connect
					.prepareStatement("delete from category where categoryid=?");
			preparedStatement.setString(1, id);
			preparedStatement.executeUpdate();
			check = "true";
		} catch (Exception e) {
			check = "false";
			e.printStackTrace();
		} finally {
			Util.close(connect,preparedStatement,rs);
		}
		System.err.println("Check = " + check);
		return check;
	}
	public static void main(String[] args) {
		CategoryDAO dao = new CategoryDAO();
		System.err.println(dao.getAllCategory().size());
	}

}
